<?php


class Oracle{

	private $conn;//连接

	private $dbname;//服务名称

	private $username;//用户名

	private $password;//密码

	private $condition;//条件

	private $table;//表名

	private $order;//排序

	private $sta;//分页开始

	private $end;//分页结束
	
	private $sql;//执行sql语句
	
	private $source;//执行原生sql语句
	

	//架构函数
 	public function __construct($table,$dbname,$username,$password) {
		$this->dbname = !empty($dbname)?$dbname:C('oracle_dbname');
		$this->username = !empty($username)?$username:C('oracle_username');
		$this->password = !empty($password)?$password:C('oracle_password');
		$this->table = !empty($table)?strtoupper($table):"";
		try{
			$this->conn = new PDO("oci:dbname=".$this->dbname,$this->username,$this->password,array(PDO_ATTR_PERSISTENT=>true));
			$this->conn->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
			$this->conn->query('set names utf8');
			}catch(PDOException $e){
				echo ("error:".$e->getMessage()."<br>");
			}
    }


	//查询返回查询结果集合
	//返回空为查询失败
	public function select(){
		$this->sql = "SELECT * from ".$this->table." ".$this->condition." ".$this->order;
		if(!empty($this->end)){
			$this->sql = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (".$this->sql.") A WHERE ROWNUM <= ".$this->end.")WHERE RN > ".$this->sta;
		}
		$arr = array();
		if(!empty($this->source)){
			$this->sql = $this->source;
			$this->source = "";
		}
		$stmt = $this->conn->prepare($this->sql);
		if($stmt->execute()){
			$i = 0;
			while($row = $stmt->fetch()){
				$thisrow = array();
				foreach ( $row as $key => $val ) {
					if(gettype($key)=="string"){
						$thisrow[$key] = iconv("gb2312",'UTF-8', $val);
					}
				}
				$arr[$i] = $thisrow;
				$i++;
			}
		}
		return $arr;
	}
	
	//返回一条数据
	public function find(){
		$this->sql = "SELECT * from ".$this->table." ".$this->condition." ".$this->order;
		$this->sql = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (".$this->sql.") A WHERE ROWNUM <= 1)WHERE RN > =0";

		$stmt = $this->conn->prepare($this->sql);
		if($stmt->execute()){
			$i = 0;
			while($row = $stmt->fetch()){
				$thisrow = array();
				foreach ( $row as $key => $val ) {
					if(gettype($key)=="string"){
						$thisrow[$key] = iconv("gb2312",'UTF-8', $val);
					}
				}
			}
		}
		return $thisrow;
	}
	
	public function findAll($sql){
		$stmt = $this->conn->prepare($sql);
		$arr = array();
		if($stmt->execute()){
			$i = 0;
			while($row = $stmt->fetch()){
				$thisrow = array();
				foreach ( $row as $key => $val ) {
					if(gettype($key)=="string"){
						$thisrow[$key] = iconv("gb2312",'UTF-8', $val);
					}
				}
				$arr[$i] = $thisrow;
				$i++;
			}
		}
		return $arr;
	}
	//
	public function findsql(){
		
		$stmt = $this->conn->prepare($this->sql);
		if($stmt->execute()){
			$i = 0;
			while($row = $stmt->fetch()){
				$thisrow = array();
				foreach ( $row as $key => $val ) {
					if(gettype($key)=="string"){
						$thisrow[$key] = iconv("gb2312",'UTF-8', $val);
					}
				}
			}
		}
		return $thisrow;
	}

	//删除数据
	//(删除数据时where()不能为空，否则系统默认为删除失败)
	//返回false为删除失败,成功返回作用数据条数
	public function delete(){
		if(empty($this->condition)){
			return false;
		}else{
			$this->sql = "delete from ".$this->table.$this->condition;
			$stmt = $this->conn->exec($this->sql);
			return $stmt;
		}
	}

	//修改数据
	//不传参数将自动匹配并修改,参数为原生sql语句
	//失败返回false，成功返回左右于数据条数
	public function save($data){
		if(empty($data)){
			if(empty($_REQUEST[$this->getPK()])){
				return false;
			}
			$bool = true;
			foreach ( $this->getField() as $key => $val ) {
				if(!empty($_REQUEST[$val])&&isset($_REQUEST[$val])){
					if($bool){
						$fie.=$val."='".$_REQUEST[$val]."'";
						$bool = false;
					}else{
						$fie.=",".$val."='".$_REQUEST[$val]."'";
					}		
				}
			}
			$this->sql = "update ".$this->table." set ".$fie." where ".$this->getPK()." = '".$_REQUEST[$this->getPK()]."'";
		}else{
			$this->sql = $data;
		}
		$stmt = $this->conn->exec($this->sql);
		return $stmt;
	}

	//插入数据
	//插入数据成功返回true,失败返回false;
	public function add(){
//		INSERT INTO BOOK(bookid,name,price) VALUES('100123','oracle sql',54.70);
		$field = "";
		$value = "";
		$bool  = true;
		foreach ( $this->getField() as $key => $val ) {
			if(!empty($_REQUEST[strtolower($val)])&&isset($_REQUEST[strtolower($val)])){
				if($bool){
					$field.=$val;
					$value.="'".$_REQUEST[strtolower($val)]."'";
					$bool = false;
				}else{
					$field.=",".$val;
					$value.=",'".$_REQUEST[strtolower($val)]."'";
				}
			}
		}

		$this->sql = "insert into ".$this->table." ( ".$field." ) values( ".$value.")";
		
		$stmt = $this->conn->exec($this->sql);
		if(!$stmt){
			return false;
		}else{
			return true;
		}
		
	}


	//条件查看数据条数
	public function count($field = '*'){

		$this->sql = "select count(".$field.") from ".$this->table.$this->condition;

		$stmt = $this->conn->prepare($this->sql);
		$count = 0;
		if($stmt->execute()){
			while($row = $stmt->fetch()){
				$count = $row[0];
			}
		}
		return $count;
	}

	
	//设置查询条件
	public function where($condition){
		$bool = true;
		$where = "";
		$strmap = $this->getStrRep();
		if(!empty($condition)){
				//参数为数组时
				if(is_array($condition)){
					foreach ( $condition as $key => $val ) {
						if (array_key_exists($val[0], $strmap)) {
							    $val[0] = $strmap[$val[0]];
							}
						if($bool){
							if($val[0]=="in"){
								$where = " where ".$key." ".$val[0]." (".$this->getSplit($val[1]).") ";
							}else{
								$where = " where ".$key." ".$val[0]." '".$val[1]."' ";
							}
						$bool = false;
						}else if($bool==false){
							if($val[0]=="in"){
								$where = $where." and ".$key." ".$val[0]." (".$this->getSplit($val[1]).") ";
							}else{
								$where = $where." and ".$key." ".$val[0]." '".$val[1]."' ";
							}
						}
				}
			}else if(is_string($condition)){
					//参数是字符串时
					$where = " where ".$condition;
				}
		}
		$this->condition = $where;
		return $this;
	}

	//设置表名
	public function table($table){
		$this->table = strtoupper($table);
		return $this;
	}
	
	//排序设置
	public function order($order){
		if(!empty($order)){
			$this->order = " order by ".$order;
		}
		return $this;
	}
	
	//分页设置
	public function limit($str){
		$array=explode(",",$str);
		$this->sta = $array[0];
		$this->end = $array[1]+$array[0];
		return $this;
	}

	
	//返回一个表的所有列名
	//返回数据不为空时查询成功，返回false时查询失败
	public function getField($table){
		$table = !empty($table)?$table:$this->table;
		if(empty($table)){
			return false;
		}
		$this->sql = "SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME = '".$table."'";
		$stmt = $this->conn->prepare(strtoupper($this->sql));
		$arr = array();
		$field = array();
		if($stmt->execute()){
			$i = 0;
			while($row = $stmt->fetch()){
				$arr[$i] = $row;
				$i++;
			}
		}
		if(empty($arr)){
			return false;
		}
		$array = array();
		$i = 0;
		foreach ( $arr as $key => $val ) {
				$array[$i] = $val['column_name'];
				$i++;
		}
		return $array;
	}

	//得到填充
	public function create(){
		foreach ( $this->getField() as $key => $value ) {
	       	
		}
	}
	
	//返回连接
	public function getConn(){
		return $this->conn;
	}
	
	//替换字符串函数
	protected function getStrRep(){
	 	return $map = array(
			'eq'=>'=',
			'neq'=>'!=',
			'gt'=>'>',
			'egt'=>'>=',
			'lt'=>'<',
			'elt'=>'<='
		);
	}
	
	//将字符串数组拼接
	function getSplit($str){
		$message = "";
		foreach ( $str as $key => $value ) {
       		if($key==0){
       			$message.="'".$value."'";
       		}else{
       			$message.=",'".$value."'";
       		}
		}
		return $message;
	}
	
	//得到某个表的主键
	function getPK(){
		$this->sql = "select col.column_name 
				from user_constraints con,  user_cons_columns col 
				where con.constraint_name = col.constraint_name 
				and con.constraint_type='P' 
				and col.table_name = '".$this->table."'";
		$stmt = $this->conn->prepare(strtoupper($this->sql));
		
		$field = array();
		if($stmt->execute()){
			$i = 0;
			while($row = $stmt->fetch()){
				$pk = $row["column_name"];
			}
		}
		return $pk;
	}
	
	//返回最终执行sql语句
	public function getSql(){
		return $this->sql;
	}
	
	//设置sql
	public function setSql($sql){
		$this->sql = $sql;
		return $this;
	}
	
	//
	public function setSource($source){
		$this->source = $source;
		return $this;
	}


}
?>